using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Threading;
using System.Xml.Serialization;

namespace Common.DLR
{
    /// <summary>
    /// 	<para>Object-relational mapping, requires field/property of object must be mapped
    ///     to table filed exactly, and object name mapped to table name.</para>
    /// 	<para>Table PK field mapping is not implemented?</para>
    /// </summary>
    public class ORMapping<T>
    {
        private Mutex _SyncObj = new Mutex(false, "ORMapping<" + typeof(T).Name + ">");
        private bool _CheckedDBSchema;
        private string _TableName;
        private string _PkFieldName;
        private bool _PkIsIdentity;

        #region datatable <-> object(s)
        /// <summary>
        /// return a datatable of single record from a object
        /// </summary>
        /// <param name="instance"></param>
        /// <returns></returns>
        public DataTable GetTableFromObject(T instance)
        {
            DataTable dtOutput = new DataTable();
            FieldInfo[] fields = instance.GetType().GetFields();
            if (fields != null && fields.Length > 0)
            {
                foreach (FieldInfo field in fields)
                {
                    if (field.IsStatic == false)
                    {
                        dtOutput.Columns.Add(field.Name, field.FieldType);
                    }
                }
            }
            PropertyInfo[] props = this.GetMappableProperties();
            if (props != null && props.Length > 0)
            {
                foreach (PropertyInfo prop in props)
                {
                    dtOutput.Columns.Add(prop.Name, prop.PropertyType);
                }
            }

            DataRow drOutput = dtOutput.NewRow();
            if (fields != null && fields.Length > 0)
            {
                foreach (FieldInfo field in fields)
                {
                    if (field.IsStatic == false)
                    {
                        object fieldValue = this.SerialzieFieldValue(field.Name, field.GetValue(instance));
                        drOutput[field.Name] = fieldValue == null ? DBNull.Value : fieldValue;
                    }
                }
                dtOutput.Rows.Add(drOutput);
            }
            if (props != null && props.Length > 0)
            {
                foreach (PropertyInfo prop in props)
                {
                    object propValue = this.SerialzieFieldValue(prop.Name, prop.GetValue(instance, null));
                    drOutput[prop.Name] = propValue == null ? DBNull.Value : propValue;
                }
            }
            return dtOutput;
        }

        /// <summary>
        /// return a list of objs from table
        /// </summary>
        /// <param name="dtData"></param>
        /// <returns></returns>
        public List<T> GetObjFromTable(DataTable dtData)
        {
            List<T> coll = new List<T>();
            if (dtData != null && dtData.Rows.Count > 0)
            {
                foreach (DataRow dr in dtData.Rows)
                {
                    T instance = (T)Activator.CreateInstance(typeof(T));

                    object temp = (object)instance;

                    FieldInfo[] fields = this.GetMappableFields();
                    foreach (FieldInfo field in fields)
                    {
                        string columnName = field.Name;
                        object[] customAttrs = field.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (customAttrs != null && customAttrs.Length == 1)
                        {
                            OrmFieldAttribute fieldAtt = (OrmFieldAttribute)customAttrs[0];
                            columnName = fieldAtt.ColumnName;
                        }

                        if (field.IsStatic == false && dtData.Columns.Contains(columnName))
                        {
                            object fieldValue = dr[columnName];
                            if (fieldValue is DBNull || fieldValue == null)
                            {
                                fieldValue = null;
                            }
                            else
                            {
                                fieldValue = this.InstantiateFieldValue(field.Name, fieldValue.ToString());
                            }
                            if (fieldValue != DBNull.Value && fieldValue != null && fieldValue.GetType() != field.FieldType)
                            {
                                fieldValue = Convert.ChangeType(fieldValue, field.FieldType);
                            }
                            if (!(fieldValue is DBNull))
                            {

                                field.SetValue(temp, fieldValue);
                            }
                        }
                    }
                    PropertyInfo[] props = this.GetMappableProperties();
                    if (props != null && props.Length > 0)
                    {
                        foreach (PropertyInfo prop in props)
                        {
                            string columnName = prop.Name;
                            object[] customAttrs = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                            if (customAttrs != null && customAttrs.Length == 1)
                            {
                                OrmFieldAttribute propAtt = (OrmFieldAttribute)customAttrs[0];
                                columnName = propAtt.ColumnName;
                            }
                            if (dtData.Columns.Contains(columnName))
                            {
                                object propValue = dr[columnName];
                                if (propValue == null || propValue is DBNull)
                                {
                                    propValue = null;
                                }
                                else
                                {
                                    propValue = this.InstantiateFieldValue(prop.Name, propValue.ToString());
                                }

                                if (propValue != DBNull.Value && propValue != null && propValue.GetType() != prop.PropertyType)
                                {
                                    propValue = Convert.ChangeType(propValue, prop.PropertyType);
                                }
                                if (!(propValue is DBNull))
                                {
                                    prop.SetValue(temp, propValue, null);
                                }
                            }
                        }
                    }

                    instance = (T)temp;
                    coll.Add(instance);
                }
            }
            return coll;
        }

        /// <summary>
        /// return a list of objs from data table with customized field mappings
        /// </summary>
        /// <param name="dtData"></param>
        /// <param name="objFieldsToTableFieldsMappings"></param>
        /// <returns></returns>
        public List<T> GetObjFromTable(DataTable dtData, Dictionary<string, string> objFieldsToTableFieldsMappings)
        {
            List<T> coll = new List<T>();
            Dictionary<string, MemberInfo> tableFieldToObjFieldMappings = new Dictionary<string, MemberInfo>();
            foreach (string objFieldName in objFieldsToTableFieldsMappings.Keys)
            {
                string colName = objFieldsToTableFieldsMappings[objFieldName];
                FieldInfo field = typeof(T).GetField(objFieldName);
                if (field != null)
                    tableFieldToObjFieldMappings.Add(colName, field);
                else
                {
                    PropertyInfo prop = typeof(T).GetProperty(objFieldName);
                    if (prop != null)
                        tableFieldToObjFieldMappings.Add(colName, prop);
                }
            }
            foreach (DataRow dr in dtData.Rows)
            {
                T instance = (T) Activator.CreateInstance(typeof(T));
                foreach (string colName in tableFieldToObjFieldMappings.Keys)
                {
                    if (dtData.Columns.Contains(colName))
                    {
                        MemberInfo member = tableFieldToObjFieldMappings[colName];
                        object fieldValue = dr[colName];
                        if (fieldValue != null)
                        {
                            fieldValue = this.InstantiateFieldValue(colName, fieldValue.ToString());
                        }
                        if (member.MemberType == MemberTypes.Field)
                        {
                            FieldInfo field = (FieldInfo) member;
                            if (fieldValue!=DBNull.Value && fieldValue != null && fieldValue.GetType() != field.FieldType)
                            {
                                fieldValue = Convert.ChangeType(fieldValue, field.FieldType);
                            }
                            field.SetValue(instance, fieldValue);
                        }
                        else if (member.MemberType == MemberTypes.Property)
                        {
                            PropertyInfo prop = (PropertyInfo) member;
                            if (fieldValue != DBNull.Value && fieldValue != null && fieldValue.GetType() != prop.PropertyType)
                            {
                                fieldValue = Convert.ChangeType(fieldValue, prop.PropertyType);
                            }
                            prop.SetValue(instance, fieldValue, null);
                        }
                    }
                }
                coll.Add(instance);
            }
            return coll;
        }

        /// <summary>
        /// return a datatable of single record from a object
        /// </summary>
        /// <param name="instances"></param>
        /// <returns></returns>
        public DataTable GetTableFromObject(List<T> instances)
        {
            DataTable dtOutput = new DataTable();
            FieldInfo[] fields = this.GetMappableFields();
            PropertyInfo[] props = this.GetMappableProperties();
            if (fields != null && fields.Length > 0)
            {
                foreach (FieldInfo field in fields)
                {
                    if (field.IsStatic == false)
                        dtOutput.Columns.Add(field.Name, field.FieldType);
                }
            }
            if (props != null && props.Length > 0)
            {
                foreach (PropertyInfo prop in props)
                {
                    dtOutput.Columns.Add(prop.Name, prop.PropertyType);
                }
            }
            foreach (T instance in instances)
            {
                DataRow drOutput = dtOutput.NewRow();
                if (fields != null && fields.Length > 0)
                {
                    foreach (FieldInfo field in fields)
                    {
                        if (!field.IsStatic)
                        {
                            object fieldValue=this.SerialzieFieldValue(field.Name, field.GetValue(instance));
                            drOutput[field.Name] = fieldValue == null ? DBNull.Value : fieldValue;
                        }
                    }
                }
                if (props != null && props.Length > 0)
                {
                    foreach (PropertyInfo prop in props)
                    {
                        object propValue=this.SerialzieFieldValue(prop.Name, prop.GetValue(instance, null));
                        drOutput[prop.Name] = propValue == null ? DBNull.Value : propValue;
                    }
                }
                dtOutput.Rows.Add(drOutput);
            }
            return dtOutput;
        }

        /// <summary>
        /// get table from list of objs with customized field mappings
        /// </summary>
        /// <param name="instances"></param>
        /// <param name="objFieldsToTableFieldsMappings"></param>
        /// <returns></returns>
        public DataTable GetTableFromObject(List<T> instances, Dictionary<string, string> objFieldsToTableFieldsMappings)
        {
            DataTable dtOutput = new DataTable();
            FieldInfo[] fields = this.GetMappableFields();
            if (fields != null && fields.Length > 0)
            {
                foreach (FieldInfo field in fields)
                {
                    if (objFieldsToTableFieldsMappings.ContainsKey(field.Name))
                    {
                        string colName = objFieldsToTableFieldsMappings[field.Name];
                        dtOutput.Columns.Add(colName, field.FieldType);
                    }
                }
            }
            PropertyInfo[] props = this.GetMappableProperties();
            if (props != null && props.Length > 0)
            {
                foreach (PropertyInfo prop in props)
                {
                    dtOutput.Columns.Add(prop.Name, prop.PropertyType);
                }
            }
            foreach (T instance in instances)
            {
                DataRow drOutput = dtOutput.NewRow();
                if (fields != null && fields.Length > 0)
                {
                    foreach (FieldInfo field in fields)
                    {
                        string colName = objFieldsToTableFieldsMappings[field.Name];
                        drOutput[colName] = this.SerialzieFieldValue(field.Name, field.GetValue(instance));
                    }
                }
                if (props != null && props.Length > 0)
                {
                    foreach (PropertyInfo prop in props)
                    {
                        drOutput[prop.Name] = this.SerialzieFieldValue(prop.Name, prop.GetValue(instance, null));
                    }
                    dtOutput.Rows.Add(drOutput);
                }
            }

            return dtOutput;
        }
        #endregion

        #region db table <-> object(s)
        /// <summary>
        /// 
        /// </summary>
        /// <param name="instance"></param>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public void SaveToDB(ref T instance, string connStr)
        {
            string tblName = typeof(T).Name;
            string pkFieldName = string.Empty;
            bool isIdentity = false;
            MemberInfo pkMember = null;
            if (!this.CheckTableExistance(connStr, ref tblName, ref pkFieldName, ref isIdentity))
            {
                this.CreateTable(connStr, ref tblName, ref pkFieldName, ref isIdentity);
            }

            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                this._SyncObj.WaitOne();
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string insertSql = "insert into [" + tblName + "] (";
                string valueSql = "(";
                FieldInfo[] fields = this.GetMappableFields();
                PropertyInfo[] props = this.GetMappableProperties();
                if (fields != null && fields.Length > 0)
                {
                    foreach (FieldInfo field in fields)
                    {
                        object fieldValue = field.GetValue(instance);

                        if (fieldValue != null)
                        {
                            SqlDbType dbType = FieldDataType.ToDbType(field.FieldType.ToString());
                            OrmFieldAttribute fieldAtt =
                                new OrmFieldAttribute(field.Name, dbType, FieldDataType.GetFieldDefaultSize(dbType),
                                                      OrmFieldSerializationMethod.DefaultSerialization);
                            object[] fieldAtts = field.GetCustomAttributes(typeof (OrmFieldAttribute), false);
                            if (fieldAtts != null && fieldAtts.Length > 0)
                            {
                                foreach (object fieldAttObj in fieldAtts)
                                {
                                    if (fieldAttObj is OrmFieldAttribute)
                                    {
                                        fieldAtt = (OrmFieldAttribute) fieldAttObj;
                                        break;
                                    }
                                }
                            }
                            if ((!isIdentity) || fieldAtt.ColumnName.ToLower() != pkFieldName.ToLower())
                            {
                                insertSql += fieldAtt.ColumnName + ",";

                                if (field.FieldType.IsEnum)
                                    dbType = SqlDbType.Int;
                                string fieldValueAsString = this.SerialzieFieldValue(field.Name, fieldValue);
                                valueSql += FieldDataType.ToMssqlDataValue(dbType, fieldValueAsString);
                                valueSql += ",";
                            }
                            if(fieldAtt.ColumnName.ToLower()==pkFieldName.ToLower())
                            {
                                pkMember = field;
                            }
                        }
                    }
                }
                if(props !=null && props.Length>0)
                {
                    foreach(PropertyInfo prop in props)
                    {
                        object propValue = prop.GetValue(instance, null);
                        if(propValue !=null)
                        {
                            SqlDbType dbType = FieldDataType.ToDbType(prop.PropertyType.ToString());
                            OrmFieldAttribute fieldAtt =
                                new OrmFieldAttribute(prop.Name, dbType, FieldDataType.GetFieldDefaultSize(dbType),
                                                      OrmFieldSerializationMethod.DefaultSerialization);
                            object[] fieldAtts = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                            if (fieldAtts != null && fieldAtts.Length > 0)
                            {
                                foreach (object fieldAttObj in fieldAtts)
                                {
                                    if (fieldAttObj is OrmFieldAttribute)
                                    {
                                        fieldAtt = (OrmFieldAttribute)fieldAttObj;
                                        break;
                                    }
                                }
                            }
                            if ((!isIdentity) || fieldAtt.ColumnName.ToLower() != pkFieldName.ToLower())
                            {
                                insertSql += fieldAtt.ColumnName + ",";

                                if (prop.PropertyType.IsEnum)
                                    dbType = SqlDbType.Int;
                                string fieldValueAsString = this.SerialzieFieldValue(prop.Name, propValue);
                                valueSql += FieldDataType.ToMssqlDataValue(dbType, fieldValueAsString);
                                valueSql += ",";
                            }
                            if(fieldAtt.ColumnName.ToLower()==pkFieldName.ToLower())
                            {
                                pkMember = prop;
                            }
                        }
                    }
                }
                if (insertSql.EndsWith(","))
                {
                    insertSql = insertSql.Substring(0, insertSql.LastIndexOf(","));
                }
                insertSql += ") values ";
                if(valueSql.EndsWith(","))
                {
                    valueSql = valueSql.Substring(0, valueSql.LastIndexOf(","));
                }
                valueSql += ")";

                cmd.CommandText = insertSql + valueSql;
                cmd.ExecuteNonQuery();

                if (isIdentity)
                {
                    //cmd.CommandText = "select max ([" + pkFieldName + "]) from [" +
                    //                  tblName + "]";
                    cmd.CommandText = "SELECT @@IDENTITY";
                    int returnValue = (int)cmd.ExecuteScalar();
                    if (pkMember != null)
                    {
                        int pkValue = int.Parse(returnValue.ToString());
                        if(pkMember.MemberType==MemberTypes.Field)
                        {
                            ((FieldInfo) pkMember).SetValue(instance, pkValue);
                        }
                        else if(pkMember.MemberType==MemberTypes.Property)
                        {
                            ((PropertyInfo) pkMember).SetValue(instance, pkValue, null);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
                this._SyncObj.ReleaseMutex();
            }
        }

        /// <summary>
        /// save list of objs to database table given table name and pk field name
        /// </summary>
        /// <param name="instances"></param>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public void SaveToDB(ref T[] instances, string connStr)
        {
            string tblName = typeof(T).Name;
            string pkFieldName = string.Empty;
            bool isIdentity = false;

            if (!this.CheckTableExistance(connStr, ref tblName, ref pkFieldName, ref isIdentity))
            {
                this.CreateTable(connStr, ref tblName, ref pkFieldName, ref isIdentity);
            }

            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                this._SyncObj.WaitOne();
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                MemberInfo pkMember = null;
                for (int i = 0; i < instances.Length; i++)
                {
                    T instance = instances[i];
                    string insertSql = "insert into [" + tblName + "] (";
                    string valueSql = "(";
                    FieldInfo[] fields = this.GetMappableFields();
                    PropertyInfo[] props = this.GetMappableProperties();
                    if (fields != null && fields.Length > 0)
                    {
                        foreach (FieldInfo field in fields)
                        {
                            object fieldValue = field.GetValue(instance);

                            if (fieldValue != null)
                            {
                                SqlDbType dbType = FieldDataType.ToDbType(field.FieldType.ToString());
                                OrmFieldAttribute fieldAtt =
                                    new OrmFieldAttribute(field.Name, dbType, FieldDataType.GetFieldDefaultSize(dbType),
                                                          OrmFieldSerializationMethod.DefaultSerialization);
                                object[] fieldAtts = field.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                                if (fieldAtts != null && fieldAtts.Length > 0)
                                {
                                    foreach (object fieldAttObj in fieldAtts)
                                    {
                                        if (fieldAttObj is OrmFieldAttribute)
                                        {
                                            fieldAtt = (OrmFieldAttribute)fieldAttObj;
                                            break;
                                        }
                                    }
                                }
                                if ((!isIdentity) || fieldAtt.ColumnName.ToLower() != pkFieldName.ToLower())
                                {
                                    insertSql += fieldAtt.ColumnName + ",";

                                    if (field.FieldType.IsEnum)
                                        dbType = SqlDbType.Int;
                                    string fieldValueAsString = this.SerialzieFieldValue(field.Name, fieldValue);
                                    valueSql += FieldDataType.ToMssqlDataValue(dbType, fieldValueAsString);
                                    valueSql += ",";
                                }
                                if (fieldAtt.ColumnName.ToLower() == pkFieldName.ToLower())
                                {
                                    pkMember = field;
                                }
                            }
                        }
                    }
                    if (props != null && props.Length > 0)
                    {
                        foreach (PropertyInfo prop in props)
                        {
                            object propValue = prop.GetValue(instance, null);
                            if (propValue != null)
                            {
                                SqlDbType dbType = FieldDataType.ToDbType(prop.PropertyType.ToString());
                                OrmFieldAttribute fieldAtt =
                                    new OrmFieldAttribute(prop.Name, dbType, FieldDataType.GetFieldDefaultSize(dbType),
                                                          OrmFieldSerializationMethod.DefaultSerialization);
                                object[] fieldAtts = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                                if (fieldAtts != null && fieldAtts.Length > 0)
                                {
                                    foreach (object fieldAttObj in fieldAtts)
                                    {
                                        if (fieldAttObj is OrmFieldAttribute)
                                        {
                                            fieldAtt = (OrmFieldAttribute)fieldAttObj;
                                            break;
                                        }
                                    }
                                }
                                if ((!isIdentity) || fieldAtt.ColumnName.ToLower() != pkFieldName.ToLower())
                                {
                                    insertSql += fieldAtt.ColumnName + ",";

                                    if (prop.PropertyType.IsEnum)
                                        dbType = SqlDbType.Int;
                                    string fieldValueAsString = this.SerialzieFieldValue(prop.Name, propValue);
                                    valueSql += FieldDataType.ToMssqlDataValue(dbType, fieldValueAsString);
                                    valueSql += ",";
                                }
                                if (fieldAtt.ColumnName.ToLower() == pkFieldName.ToLower())
                                {
                                    pkMember = prop;
                                }
                            }
                        }
                    }
                    if (insertSql.EndsWith(","))
                    {
                        insertSql = insertSql.Substring(0, insertSql.LastIndexOf(","));
                    }
                    insertSql += ") values ";
                    if (valueSql.EndsWith(","))
                    {
                        valueSql = valueSql.Substring(0, valueSql.LastIndexOf(","));
                    }
                    valueSql += ")";

                    cmd.CommandText = insertSql + valueSql;
                    cmd.ExecuteNonQuery();

                    if (isIdentity)
                    {
                        //cmd.CommandText = "select max ([" + pkFieldName + "]) from [" +
                        //                  tblName + "]";
                        cmd.CommandText = "SELECT @@IDENTITY";
                        int returnValue = (int)cmd.ExecuteScalar();
                        if (pkMember != null)
                        {
                            int pkValue = int.Parse(returnValue.ToString());
                            if (pkMember.MemberType == MemberTypes.Field)
                            {
                                ((FieldInfo) pkMember).SetValue(instance, pkValue);
                            }
                            else if (pkMember.MemberType == MemberTypes.Property)
                            {
                                ((PropertyInfo) pkMember).SetValue(instance, pkValue, null);
                            }
                        }
                    }
                    instances[i] = instance;
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                this._SyncObj.ReleaseMutex();
                conn.Close();
            }
        }

        /// <summary>get object from table by key</summary>
        public T GetObjByKey(string connStr, string keyFieldValue)
        {
            string tblName = typeof(T).Name;
            string keyFieldName = string.Empty;
            bool isIdentity = false;
            if (!this.CheckTableExistance(connStr, ref tblName, ref keyFieldName, ref isIdentity))
            {
                this.CreateTable(connStr, ref tblName, ref keyFieldName, ref isIdentity);
            }

            TableColumn keyCol = DbUtil.GetTableColumn(connStr, tblName, keyFieldName);
            if (string.IsNullOrEmpty(keyCol.ColumnName))
                throw new Exception("Unable to get corresponding table");

            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(string.Format("select * from [{0}] where [{1}]={2}",
                    tblName, keyFieldName,
                    FieldDataType.ToMssqlDataValue(keyCol.DbType, keyFieldValue)),
                    conn);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            if (dt.Rows.Count > 0)
            {
                List<T> objs = this.GetObjFromTable(dt);
                if (objs != null && objs.Count > 0)
                    return objs[0];
                else
                    return default(T);
            }
            else
                return default(T);
        }

        /// <summary>get field value from object</summary>
        public string GetSerializedObjFieldValue(T obj, string colName)
        {
            FieldInfo[] fields = this.GetMappableFields();
            if (fields != null && fields.Length > 0)
            {
                foreach (FieldInfo fi in fields)
                {
                    SqlDbType dbType = FieldDataType.ToDbType(fi.FieldType.ToString());
                    OrmFieldAttribute fieldAtt = new OrmFieldAttribute(fi.Name,
                        dbType, FieldDataType.GetFieldDefaultSize(dbType),
                        OrmFieldSerializationMethod.DefaultSerialization);
                    object[] atts = fi.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (atts != null && atts.Length > 0)
                    {
                        foreach (object att in atts)
                        {
                            if (att is OrmFieldAttribute)
                            {
                                fieldAtt = (OrmFieldAttribute) att;
                                break;
                            }
                        }
                    }
                    if (fieldAtt.ColumnName.ToLower() == colName.ToLower())
                    {
                        object val = fi.GetValue(obj);
                        if (val != null)
                        {
                            return this.SerialzieFieldValue(fi.Name, val);
                        }
                    }
                }
            }
            PropertyInfo[] props = this.GetMappableProperties();
            if (props != null && props.Length > 0)
            {
                foreach (PropertyInfo prop in props)
                {
                    SqlDbType dbType = FieldDataType.ToDbType(prop.PropertyType.ToString());
                    OrmFieldAttribute fieldAtt = new OrmFieldAttribute(prop.Name,
                        dbType, FieldDataType.GetFieldDefaultSize(dbType),
                        OrmFieldSerializationMethod.DefaultSerialization);
                    object[] atts = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (atts != null && atts.Length > 0)
                    {
                        foreach (object att in atts)
                        {
                            if (att is OrmFieldAttribute)
                            {
                                fieldAtt = (OrmFieldAttribute) att;
                                break;
                            }
                        }
                    }
                    if (fieldAtt.ColumnName.ToLower() == colName.ToLower())
                    {
                        object val = prop.GetValue(obj, null);
                        if (val != null)
                        {
                            return this.SerialzieFieldValue(prop.Name, val);
                        }
                    }
                }
            }

            return null;
        }

        /// <summary>return a list of object from table</summary>
        public List<T> GetAll(string connStr)
        {
            string tblName = typeof(T).Name;
            string pkFieldName = string.Empty;
            bool isIdentity = false;

            if (!this.CheckTableExistance(connStr, ref tblName, ref pkFieldName, ref isIdentity))
            {
                this.CreateTable(connStr, ref tblName, ref pkFieldName, ref isIdentity);
            }
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(string.Format("select * from [{0}]",
                    tblName), conn);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            if (dt.Rows.Count > 0)
            {
                List<T> objs = this.GetObjFromTable(dt);
                return objs;
            }
            else
                return null;
        }

        /// <summary>
        /// update table record with object, if not exist (checked by key), insert a new
        /// entry into table.
        /// </summary>
        public void Update(string connStr, ref T obj)
        {
            string tblName = typeof(T).Name;
            string keyFieldName = string.Empty;
            bool isIdentity = false;

            if (!this.CheckTableExistance(connStr, ref tblName, ref keyFieldName, ref isIdentity))
            {
                this.CreateTable(connStr, ref tblName, ref keyFieldName, ref isIdentity);
            }

            TableColumn keyCol = DbUtil.GetTableColumn(connStr, tblName, keyFieldName);
            if (string.IsNullOrEmpty(keyCol.ColumnName))
                throw new Exception("Unable to get corresponding table");
            string keyValue = this.GetSerializedObjFieldValue(obj, keyFieldName);
            if (string.IsNullOrEmpty(keyValue))
                throw new Exception("Unable to get key value");
            TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, string.Empty, tblName);

            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                this._SyncObj.WaitOne();
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = string.Format("select count (*) from {0} where {1}={2}",
                    tblName, keyFieldName,
                    FieldDataType.ToMssqlDataValue(keyCol.DbType, keyValue));
                bool exist = false;
                try
                {
                    int count = int.Parse(cmd.ExecuteScalar().ToString());
                    if (count > 0)
                        exist = true;
                }
                catch
                {
                    exist = false;
                }
                string sql = string.Empty;
                if (exist)
                {
                    sql = "update " + tblName + " set ";
                    foreach (TableColumn col in cols)
                    {
                        if (col.ColumnName.ToLower() != keyFieldName.ToLower())
                        {
                            sql += col.ColumnName + "=" + FieldDataType.ToMssqlDataValue(col.DbType,
                                this.GetSerializedObjFieldValue(obj, col.ColumnName)) + ",";
                        }
                    }
                    if (sql.EndsWith(","))
                    {
                        sql = sql.Substring(0, sql.Length - 1);
                    }
                    sql += " where " + keyFieldName + "=" + FieldDataType.ToMssqlDataValue(keyCol.DbType,
                        this.GetSerializedObjFieldValue(obj, keyFieldName));
                }
                else
                {
                    sql = "insert into " + tblName + "(";
                    foreach (TableColumn col in cols)
                    {
                        if (!col.IsIdentity)
                            sql += col.ColumnName + ",";
                    }
                    if (sql.EndsWith(","))
                    {
                        sql = sql.Substring(0, sql.Length - 1);
                    }
                    sql += ") values (";
                    foreach (TableColumn col in cols)
                    {
                        if (!col.IsIdentity)
                        {
                            sql += FieldDataType.ToMssqlDataValue(col.DbType, this.GetSerializedObjFieldValue(obj, col.ColumnName)) + ",";
                        }
                    }
                    if (sql.EndsWith(","))
                    {
                        sql = sql.Substring(0, sql.Length - 1);
                    }
                    sql += ")";
                }
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                if (isIdentity)
                {
                    // cmd.CommandText = "select max (" + keyFieldName + ") from " + tblName;
                    cmd.CommandText = "SELECT @@IDENTITY";
                    int pkValue = int.Parse(cmd.ExecuteScalar().ToString());
                    MemberInfo pkMemberInfo = this.GetPkFieldMemberInfo();
                    if (pkMemberInfo != null)
                    {
                        if (pkMemberInfo.MemberType == MemberTypes.Field)
                        {
                            ((FieldInfo)pkMemberInfo).SetValue(obj, pkValue);
                        }
                        else if (pkMemberInfo.MemberType == MemberTypes.Property)
                        {
                            ((PropertyInfo)pkMemberInfo).SetValue(obj, pkValue, null);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
                this._SyncObj.ReleaseMutex();
            }
        }

        /// <summary>delete record by key</summary>
        public void DeleteObjByKey(string connStr, string keyFieldValue)
        {
            string tblName = typeof(T).Name;
            string pkFieldName = string.Empty;
            bool isIdentity = false;
            if (!this.CheckTableExistance(connStr, ref tblName, ref pkFieldName, ref isIdentity))
            {
                this.CreateTable(connStr, ref tblName, ref pkFieldName, ref isIdentity);
            }
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = conn.CreateCommand();

            try
            {
                this._SyncObj.WaitOne();
                conn.Open();
                Dictionary<string, List<MemberInfo>> childRefs = this.GetChildrenReferences();
                if (childRefs != null && childRefs.ContainsKey(pkFieldName))
                {
                    List<MemberInfo> childMmebers = childRefs[pkFieldName];
                    foreach (MemberInfo childMember in childMmebers)
                    {
                        Dictionary<string, string> memberNameToColName = this.GetMemberColumnNameMappingsForType(childMember.DeclaringType);
                        OrmClassAttribute classAtt = (OrmClassAttribute)childMember.DeclaringType.GetCustomAttributes(typeof(OrmClassAttribute), false)[0];
                        string childTableName = classAtt.TableName;
                        string childColumnName = memberNameToColName[childMember.Name];
                        TableColumn childKeyCol = DbUtil.GetTableColumn(connStr, childTableName, childColumnName);
                        if (string.IsNullOrEmpty(childKeyCol.ColumnName))
                            throw new Exception("Unable to get corresponding table");

                        cmd.CommandText = string.Format("delete {0} where {1}={2}",
                            childTableName, childKeyCol.ColumnName,
                            FieldDataType.ToMssqlDataValue(childKeyCol.DbType, keyFieldValue));
                        cmd.ExecuteNonQuery();
                    }
                }

                TableColumn keyCol = DbUtil.GetTableColumn(connStr, tblName, pkFieldName);
                if (string.IsNullOrEmpty(keyCol.ColumnName))
                    throw new Exception("Unable to get corresponding table");

                cmd.CommandText = string.Format("delete {0} where {1}={2}",
                    tblName, keyCol.ColumnName,
                    FieldDataType.ToMssqlDataValue(keyCol.DbType, keyFieldValue));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                this._SyncObj.ReleaseMutex();
                conn.Close();
            }
        }

        private bool CheckTableExistance(string connStr, ref string tblName, ref string pkFieldName, ref bool isIdentity)
        {
            if (this._CheckedDBSchema)
            {
                tblName = this._TableName;
                pkFieldName = this._PkFieldName;
                isIdentity = this._PkIsIdentity;
                return true;
            }
            else
            {
                object[] atts =
                    typeof (T).GetCustomAttributes(typeof (OrmClassAttribute), false);
                OrmClassAttribute classAtt = null;
                if (atts != null && atts.Length > 0)
                {
                    foreach (object att in atts)
                    {
                        if (att is OrmClassAttribute)
                        {
                            classAtt = (OrmClassAttribute) att;
                            break;
                        }
                    }
                }
                if (classAtt == null)
                    throw new Exception("Object must be decorated with OrmClass attribute in order to use ORM");

                tblName = classAtt.TableName;
                pkFieldName = classAtt.PkFieldName;
                isIdentity = classAtt.IsPkIdentityField;

                this._CheckedDBSchema = DbUtil.IsTableExist(DataProviderType.MSSQL, connStr, tblName);
                this._TableName = tblName;
                this._PkFieldName = pkFieldName;
                this._PkIsIdentity = isIdentity;
                return this._CheckedDBSchema;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="pkFieldName"></param>
        /// <param name="isPkFieldIdentity"></param>
        private void CreateTable(string connStr, ref string tblName, ref string pkFieldName, ref bool isPkFieldIdentity)
        {
            TableColumn[] cols = this.ExtractTableSchema(ref tblName, ref pkFieldName, ref isPkFieldIdentity);
            TableHelper.CreateTable(connStr, tblName, cols, false);
        }

        private TableColumn[] ExtractTableSchema(ref string tblName, ref string pkFieldName, ref bool isPkIdentity)
        {
            object[] atts =
                typeof(T).GetCustomAttributes(typeof(OrmClassAttribute), false);
            OrmClassAttribute classAtt = null;
            if (atts != null && atts.Length > 0)
            {
                foreach (object att in atts)
                {
                    if (att is OrmClassAttribute)
                    {
                        classAtt = (OrmClassAttribute) att;
                        break;
                    }
                }
            }
            if (classAtt == null)
                throw new Exception("Object must be decorated with OrmClass attribute in order to use ORM");

            tblName = classAtt.TableName;
            pkFieldName = classAtt.PkFieldName;
            isPkIdentity = classAtt.IsPkIdentityField;
            List<TableColumn> cols = new List<TableColumn>();
            FieldInfo[] fields = this.GetMappableFields();
            if (fields != null && fields.Length > 0)
            {
                foreach (FieldInfo field in fields)
                {
                    if (field.IsPublic)
                    {
                        TableColumn col = new TableColumn();
                        col.AllowNull = true;
                        col.ColumnName = field.Name;
                        if (field.FieldType.IsEnum)
                        {
                            col.DbType = SqlDbType.Int;
                        }
                        else
                        {
                            col.DbType = FieldDataType.ToDbType(field.FieldType.ToString());
                        }
                        col.IsFK = false;
                        col.IsIdentity = false;
                        col.IsIndexed = false;
                        col.IsPK = false;
                        col.Ordinal = cols.Count;
                        col.Precision = 0;
                        col.Scale = 0;
                        col.Size = FieldDataType.GetFieldDefaultSize(col.DbType);
                        OrmFieldAttribute ormFieldAtt =
                            new OrmFieldAttribute(col.ColumnName, col.DbType, col.Size,
                                                  OrmFieldSerializationMethod.DefaultSerialization);
                        object[] fieldAttrs = field.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (fieldAttrs != null && fieldAttrs.Length > 0)
                        {
                            foreach (object fieldAtt in fieldAttrs)
                            {
                                if (fieldAtt is OrmFieldAttribute)
                                {
                                    ormFieldAtt = (OrmFieldAttribute) fieldAtt;
                                    break;
                                }
                            }
                        }

                        if (ormFieldAtt.Mappable)
                        {
                            if (!string.IsNullOrEmpty(ormFieldAtt.ColumnName))
                                col.ColumnName = ormFieldAtt.ColumnName;
                            col.DbType = ormFieldAtt.FieldDataType;
                            col.Size = ormFieldAtt.Size;
                            if (col.ColumnName.ToLower() == pkFieldName.ToLower())
                            {
                                col.IsPK = true;
                                col.IsIndexed = true;
                                if (isPkIdentity)
                                {
                                    col.IsIdentity = true;
                                }
                            }
                            //if(ormFieldAtt.SerializationMethod==OrmFieldSerializationMethod.BinarySerialization)
                            //{
                            //    col.DbType = SqlDbType.Text;
                            //}
                            cols.Add(col);
                        }
                    }
                }
            }

            PropertyInfo[] props = this.GetMappableProperties();
            if (props != null && props.Length > 0)
            {
                foreach (PropertyInfo prop in props)
                {
                    if (prop.CanRead && prop.CanWrite)
                    {
                        TableColumn col = new TableColumn();
                        col.AllowNull = true;
                        col.ColumnName = prop.Name;
                        if (prop.PropertyType.IsEnum)
                        {
                            col.DbType = SqlDbType.Int;
                        }
                        else
                        {
                            col.DbType = FieldDataType.ToDbType(prop.PropertyType.ToString());
                        }
                        col.IsFK = false;
                        col.IsIdentity = false;
                        col.IsIndexed = false;
                        col.IsPK = false;
                        col.Ordinal = cols.Count;
                        col.Precision = 0;
                        col.Scale = 0;
                        col.Size = FieldDataType.GetFieldDefaultSize(col.DbType);
                        OrmFieldAttribute ormFieldAtt =
                            new OrmFieldAttribute(col.ColumnName, col.DbType, col.Size,
                                                  OrmFieldSerializationMethod.DefaultSerialization);
                        object[] fieldAttrs = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (fieldAttrs != null && fieldAttrs.Length > 0)
                        {
                            foreach (object fieldAtt in fieldAttrs)
                            {
                                if (fieldAtt is OrmFieldAttribute)
                                {
                                    ormFieldAtt = (OrmFieldAttribute) fieldAtt;
                                    break;
                                }
                            }
                        }

                        if (ormFieldAtt.Mappable)
                        {
                            if (!string.IsNullOrEmpty(ormFieldAtt.ColumnName))
                                col.ColumnName = ormFieldAtt.ColumnName;
                            col.DbType = ormFieldAtt.FieldDataType;
                            col.Size = ormFieldAtt.Size;
                            if (col.ColumnName.ToLower() == pkFieldName.ToLower())
                            {
                                col.IsPK = true;
                                col.IsIndexed = true;
                                if (isPkIdentity)
                                {
                                    col.IsIdentity = true;
                                }
                            }
                            //if (ormFieldAtt.SerializationMethod == OrmFieldSerializationMethod.BinarySerialization)
                            //{
                            //    col.DbType = SqlDbType.Text;
                            //}
                            cols.Add(col);
                        }
                    }
                }
            }
            return cols.ToArray();
        }

        private MemberInfo GetPkFieldMemberInfo()
        {
            string pkFieldName=string.Empty;
            object[] classAttrs = typeof(T).GetCustomAttributes(typeof(OrmClassAttribute), false);
            if(classAttrs !=null && classAttrs.Length==1)
            {
                pkFieldName=((OrmClassAttribute)classAttrs[0]).PkFieldName;
            }
            Debug.Assert(!string.IsNullOrEmpty(pkFieldName), "ORM class must be decorated by [OrmClassAttribute]");

            MemberInfo[] members = typeof(T).GetMembers();
            foreach (MemberInfo member in members)
            {
                if(member.Name==pkFieldName)
                    return member;
                else
                {
                    if(member.MemberType==MemberTypes.Field)
                    {
                        FieldInfo field=(FieldInfo)member;
                        object[] fieldAttrs=field.GetCustomAttributes(typeof(OrmFieldAttribute),false);
                        if (fieldAttrs != null && fieldAttrs.Length == 1)
                        {
                            OrmFieldAttribute fieldAtt = (OrmFieldAttribute)fieldAttrs[0];
                            if (fieldAtt.ColumnName == pkFieldName)
                            {
                                return field;
                            }
                        }
                    }
                    else if(member.MemberType==MemberTypes.Property)
                    {
                        PropertyInfo prop = (PropertyInfo)member;
                        object[] propAttrs = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (propAttrs != null && propAttrs.Length == 1)
                        {
                            OrmFieldAttribute propAtt = (OrmFieldAttribute)propAttrs[0];
                            if (propAtt.ColumnName == pkFieldName)
                            {
                                return prop;
                            }
                        }
                    }
                }
            }
            return null;
        }

        private Dictionary<string,MemberInfo> GetColumnNameMemberMappingsForType(Type type)
        {
            Dictionary<string, MemberInfo> columnNameToMemberMappings = new Dictionary<string, MemberInfo>();
            FieldInfo[] parentFields = type.GetFields();
            if (parentFields != null && parentFields.Length > 0)
            {
                foreach (FieldInfo field in parentFields)
                {
                    object[] fieldAtts = field.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (fieldAtts != null && fieldAtts.Length == 1)
                    {
                        OrmFieldAttribute fieldAtt = (OrmFieldAttribute)fieldAtts[0];
                        columnNameToMemberMappings.Add(fieldAtt.ColumnName, field);
                    }
                    else
                    {
                        columnNameToMemberMappings.Add(field.Name, field);
                    }
                }
            }
            PropertyInfo[] props = type.GetProperties();
            if (props != null && props.Length == 1)
            {
                foreach (PropertyInfo prop in props)
                {
                    object[] propsAtts = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (propsAtts != null && propsAtts.Length == 1)
                    {
                        OrmFieldAttribute propAtt = (OrmFieldAttribute)propsAtts[0];
                        columnNameToMemberMappings.Add(propAtt.ColumnName, prop);
                    }
                    else
                    {
                        columnNameToMemberMappings.Add(prop.Name, prop);
                    }
                }
            }
            return columnNameToMemberMappings;
        }

        private Dictionary<string,string> GetMemberColumnNameMappingsForType(Type type)
        {
            Dictionary<string, string> memberToColumnMappings = new Dictionary<string, string>();
            FieldInfo[] parentFields = type.GetFields();
            if (parentFields != null && parentFields.Length > 0)
            {
                foreach (FieldInfo field in parentFields)
                {
                    object[] fieldAtts = field.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (fieldAtts != null && fieldAtts.Length == 1)
                    {
                        OrmFieldAttribute fieldAtt = (OrmFieldAttribute)fieldAtts[0];
                        memberToColumnMappings.Add(field.Name, fieldAtt.ColumnName);
                    }
                    else
                    {
                        memberToColumnMappings.Add(field.Name, field.Name);
                    }
                }
            }
            PropertyInfo[] props = type.GetProperties();
            if (props != null && props.Length == 1)
            {
                foreach (PropertyInfo prop in props)
                {
                    object[] propsAtts = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (propsAtts != null && propsAtts.Length == 1)
                    {
                        OrmFieldAttribute propAtt = (OrmFieldAttribute)propsAtts[0];
                        memberToColumnMappings.Add(prop.Name, propAtt.ColumnName);
                    }
                    else
                    {
                        memberToColumnMappings.Add(prop.Name, prop.Name);
                    }
                }
            }
            return memberToColumnMappings;
        }
        #endregion

        #region searches
        /// <summary>
        /// given where clause (excluding WHERE key word), return a list of 
        /// entities (note: no joins)
        /// </summary>
        /// <Node>No Table Joins</Node>
        /// <param name="connStr"></param>
        /// <param name="whereClause"></param>
        /// <returns></returns>
        public List<T> Search(string connStr, string whereClause)
        {
            string tblName = typeof(T).Name;
            string pkFieldName = string.Empty;
            bool isIdentity = false;

            if (!this.CheckTableExistance(connStr, ref tblName, ref pkFieldName, ref isIdentity))
            {
                this.CreateTable(connStr, ref tblName, ref pkFieldName, ref isIdentity);
            }
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(string.Format("select * from [{0}]",
                    tblName + " where " + whereClause), conn);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            if (dt.Rows.Count > 0)
            {
                List<T> objs = this.GetObjFromTable(dt);
                return objs;
            }
            else
                return null;
        }
        #endregion

        #region field serialization and instantiation
        private FieldInfo[] GetMappableFields()
        {
            List<FieldInfo> fieldInfos = new List<FieldInfo>();
            FieldInfo[] fields = typeof(T).GetFields();
            if (fields != null && fields.Length > 0)
            {
                foreach (FieldInfo field in fields)
                {
                    if (field.IsPublic)
                    {
                        OrmFieldAttribute ormFieldAtt =
                            new OrmFieldAttribute(field.Name, SqlDbType.VarChar, 50,
                                                  OrmFieldSerializationMethod.DefaultSerialization);
                        object[] fieldAttrs = field.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (fieldAttrs != null && fieldAttrs.Length > 0)
                        {
                            foreach (object fieldAtt in fieldAttrs)
                            {
                                if (fieldAtt is OrmFieldAttribute)
                                {
                                    ormFieldAtt = (OrmFieldAttribute) fieldAtt;
                                    break;
                                }
                            }
                        }
                        if (ormFieldAtt.Mappable)
                        {
                            fieldInfos.Add(field);
                        }
                    }
                }
            }
            return fieldInfos.ToArray();
        }

        private PropertyInfo[] GetMappableProperties()
        {
            List<PropertyInfo> propInfos = new List<PropertyInfo>();
            PropertyInfo[] props = typeof(T).GetProperties();
            if (props != null && props.Length > 0)
            {
                foreach (PropertyInfo prop in props)
                {
                    if (prop.CanRead && prop.CanWrite)
                    {
                        OrmFieldAttribute ormFieldAtt =
                            new OrmFieldAttribute(prop.Name, SqlDbType.VarChar, 50,
                                                  OrmFieldSerializationMethod.DefaultSerialization);
                        object[] fieldAttrs = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (fieldAttrs != null && fieldAttrs.Length > 0)
                        {
                            foreach (object fieldAtt in fieldAttrs)
                            {
                                if (fieldAtt is OrmFieldAttribute)
                                {
                                    ormFieldAtt = (OrmFieldAttribute) fieldAtt;
                                    break;
                                }
                            }
                        }
                        if (ormFieldAtt.Mappable)
                        {
                            propInfos.Add(prop);
                        }
                    }
                }
            }
            return propInfos.ToArray();
        }

        private string SerialzieFieldValue(string fieldName, object fieldValue)
        {
            Type fieldType = typeof(string);
            OrmFieldAttribute ormFieldAtt =
                new OrmFieldAttribute(fieldName, SqlDbType.VarChar, 50, OrmFieldSerializationMethod.DefaultSerialization);
            FieldInfo fieldInfo = typeof(T).GetField(fieldName);
            if (fieldInfo != null)
            {
                fieldType = fieldInfo.FieldType;
                ormFieldAtt.FieldDataType = FieldDataType.ToDbType(fieldInfo.FieldType.ToString());
                if (fieldInfo.FieldType.IsEnum)
                {
                    fieldValue = (int) fieldValue;
                    ormFieldAtt.FieldDataType = SqlDbType.Int;
                }
                ormFieldAtt.Size = FieldDataType.GetFieldDefaultSize(ormFieldAtt.FieldDataType);

                object[] fieldAtts = fieldInfo.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                if (fieldAtts != null && fieldAtts.Length > 0)
                {
                    foreach (object fieldAtt in fieldAtts)
                    {
                        if (fieldAtt is OrmFieldAttribute)
                        {
                            ormFieldAtt = (OrmFieldAttribute) fieldAtt;
                            break;
                        }
                    }
                }
            }
            else
            {
                PropertyInfo prop = typeof(T).GetProperty(fieldName);
                if (prop != null)
                {
                    fieldType = prop.PropertyType;
                    ormFieldAtt.FieldDataType = FieldDataType.ToDbType(prop.PropertyType.ToString());
                    if (prop.PropertyType.IsEnum)
                    {
                        ormFieldAtt.FieldDataType = SqlDbType.Int;
                        fieldValue = (int) fieldValue;
                    }
                    ormFieldAtt.Size = FieldDataType.GetFieldDefaultSize(ormFieldAtt.FieldDataType);
                    object[] propAtts = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (propAtts != null && propAtts.Length > 0)
                    {
                        foreach (object propAtt in propAtts)
                        {
                            if (propAtt is OrmFieldAttribute)
                            {
                                ormFieldAtt = (OrmFieldAttribute) propAtt;
                                break;
                            }
                        }
                    }
                }
                else
                {
                    throw new Exception("Unable to find matching member for field " + fieldName);
                }
            }
            string value = null;
            if (fieldValue != null && fieldValue.GetType() != fieldType && fieldValue.GetType().IsSubclassOf(fieldType))
            {
                fieldType = fieldValue.GetType();
            }

            switch (ormFieldAtt.SerializationMethod)
            {
                case OrmFieldSerializationMethod.DefaultSerialization:
                    value =
                        SimpleDataTypeUtil.ReadValueAsString(
                            SimpleDataTypeUtil.ToSimpleDataType(FieldDataType.ToSystemType(ormFieldAtt.FieldDataType)),
                            fieldValue);
                    break;
                case OrmFieldSerializationMethod.XmlSerialization:
                    XmlSerializer serializer = new XmlSerializer(fieldType);
                    StringBuilder buffer = new StringBuilder();
                    StringWriter sWriter = new StringWriter(buffer);
                    serializer.Serialize(sWriter, fieldValue);
                    sWriter.Flush();
                    value = buffer.ToString();


                    break;
                case OrmFieldSerializationMethod.BinarySerialization:
                    BinaryFormatter bSerializer = new BinaryFormatter();
                    string tmpFilePath = FileUtil.GetWindowsAppTempFilePath("ORMapping", ".orm");
                    FileStream fs = File.OpenWrite(tmpFilePath);
                    bSerializer.Serialize(fs, fieldValue);
                    fs.Close();

                    fs = File.OpenRead(tmpFilePath);
                    byte[] contents = new byte[fs.Length];
                    fs.Read(contents, 0, contents.Length);
                    fs.Close();
                    value = Base64Util.EncodeAsBase64(contents);

                    File.Delete(tmpFilePath);


                    break;
                case OrmFieldSerializationMethod.CustomSerializationMethod:
                    throw new NotImplementedException();
                default:
                    break;
            }
            return value;
        }

        private object InstantiateFieldValue(string fieldName, string fieldValue)
        {
            Type fieldType = typeof(string);
            OrmFieldAttribute ormFieldAtt =
                new OrmFieldAttribute(fieldName, SqlDbType.VarChar, 50, OrmFieldSerializationMethod.DefaultSerialization);
            FieldInfo fieldInfo = typeof(T).GetField(fieldName);
            if (fieldInfo != null)
            {
                fieldType = fieldInfo.FieldType;
                ormFieldAtt.FieldDataType = FieldDataType.ToDbType(fieldInfo.FieldType.ToString());
                if (fieldInfo.FieldType.IsEnum)
                {
                    ormFieldAtt.FieldDataType = SqlDbType.Int;
                }
                ormFieldAtt.Size = FieldDataType.GetFieldDefaultSize(ormFieldAtt.FieldDataType);

                object[] fieldAtts = fieldInfo.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                if (fieldAtts != null && fieldAtts.Length > 0)
                {
                    foreach (object fieldAtt in fieldAtts)
                    {
                        if (fieldAtt is OrmFieldAttribute)
                        {
                            ormFieldAtt = (OrmFieldAttribute) fieldAtt;
                            break;
                        }
                    }
                }
            }
            else
            {
                PropertyInfo prop = typeof(T).GetProperty(fieldName);
                if (prop != null)
                {
                    fieldType = prop.PropertyType;
                    ormFieldAtt.FieldDataType = FieldDataType.ToDbType(prop.PropertyType.ToString());
                    if (prop.PropertyType.IsEnum)
                        ormFieldAtt.FieldDataType = SqlDbType.Int;
                    ormFieldAtt.Size = FieldDataType.GetFieldDefaultSize(ormFieldAtt.FieldDataType);
                    object[] propAtts = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (propAtts != null && propAtts.Length > 0)
                    {
                        foreach (object propAtt in propAtts)
                        {
                            if (propAtt is OrmFieldAttribute)
                            {
                                ormFieldAtt = (OrmFieldAttribute) propAtt;
                                break;
                            }
                        }
                    }
                }
                else
                {
                    throw new Exception("Unable to find matching member for field " + fieldName);
                }
            }
            object value = null;
            if (fieldValue != null && fieldValue.GetType() != fieldType && fieldValue.GetType().IsSubclassOf(fieldType))
            {
                fieldType = fieldValue.GetType();
            }

            switch (ormFieldAtt.SerializationMethod)
            {
                case OrmFieldSerializationMethod.DefaultSerialization:
                    if (fieldType.IsEnum)
                    {
                        value = Enum.ToObject(fieldType, int.Parse(fieldValue));
                    }
                    else
                    {
                        value =
                            SimpleDataTypeUtil.ToDbValue(
                                SimpleDataTypeUtil.ToSimpleDataType(
                                    FieldDataType.ToSystemType(ormFieldAtt.FieldDataType)),
                                fieldValue);
                    }
                    break;
                case OrmFieldSerializationMethod.XmlSerialization:
                    XmlSerializer serializer = new XmlSerializer(fieldType);
                    StringReader sReader = new StringReader(fieldValue);
                    value = serializer.Deserialize(sReader);
                    break;
                case OrmFieldSerializationMethod.BinarySerialization:
                    BinaryFormatter bSerializer2 = new BinaryFormatter();
                    string tmpFilePath = FileUtil.GetWindowsAppTempFilePath("ORMapping", ".orm");
                    FileStream fs2 = File.OpenWrite(tmpFilePath);
                    byte[] contents2 = Base64Util.DecodeAsBytes(fieldValue);
                    fs2.Write(contents2, 0, contents2.Length);
                    fs2.Flush();
                    fs2.Close();
                    fs2 = File.OpenRead(tmpFilePath);
                    value = bSerializer2.Deserialize(fs2);
                    fs2.Close();
                    File.Delete(tmpFilePath);
                    break;
                case OrmFieldSerializationMethod.CustomSerializationMethod:
                    throw new NotImplementedException();
                default:
                    break;
            }
            return value;
        }
        #endregion

        #region references
        /// <summary>
        /// 
        /// </summary>
        /// <returns>dictionary of member name of current type --> parent reference</returns>
        public Dictionary<string, OrmForeignKey> GetParentReferences()
        {
            
                Dictionary<string, OrmForeignKey> parentTypes = new Dictionary<string, OrmForeignKey>();
                FieldInfo[] fields = this.GetMappableFields();
                if(fields !=null && fields.Length>0)
                {
                    foreach(FieldInfo field in fields)
                    {
                        object[] fieldAttrs = field.GetCustomAttributes(typeof (OrmForeignKey), false);
                        if(fieldAttrs !=null && fieldAttrs.Length==1)
                        {
                            OrmForeignKey foreignKey = (OrmForeignKey) fieldAttrs[0];
                            parentTypes.Add(field.Name, foreignKey);
                        }
                    }
                }
                PropertyInfo[] props = this.GetMappableProperties();
                if(props !=null && props.Length>0)
                {
                    foreach(PropertyInfo prop in props)
                    {
                        object[] propAttrs = prop.GetCustomAttributes(typeof (OrmForeignKey), false);
                        if(propAttrs !=null && propAttrs.Length==1)
                        {
                            OrmForeignKey foreignKey = (OrmForeignKey) propAttrs[0];
                            parentTypes.Add(prop.Name, foreignKey);
                        }
                    }
                }

                return parentTypes;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="parentType"></param>
        /// <param name="parentKeyValue"></param>
        /// <returns></returns>
        public object GetReferencedParent(string connStr, Type parentType, string parentKeyValue)
        {
            object parent = null;
            Dictionary<string, OrmForeignKey> parentRefs = this.GetParentReferences();
            Dictionary<string, string> memberColumnMappings = this.GetMemberColumnNameMappingsForType(typeof (T));
            foreach (string memberName in parentRefs.Keys)
            {
                if (parentRefs[memberName].ParentType == parentType)
                {
                    OrmClassAttribute ormClassAttr =
                        (OrmClassAttribute) parentType.GetCustomAttributes(typeof (OrmClassAttribute), false)[0];
                    string parentTableName = ormClassAttr.TableName;
                    string parentKeyColumnName = memberColumnMappings[parentRefs[memberName].ParentMemberName];
                    TableColumn parentKeyCol = DbUtil.GetTableColumn(connStr, parentTableName, parentKeyColumnName);
                    SqlConnection conn = new SqlConnection(connStr);
                    try
                    {
                        conn.Open();
                        SqlCommand cmd = conn.CreateCommand();
                        cmd.CommandText = "select * from " + parentTableName + " where " +
                                          parentKeyColumnName + "=" +
                                          FieldDataType.ToMssqlDataValue(parentKeyCol.DbType, parentKeyValue);
                        SqlDataAdapter daSelect = new SqlDataAdapter(cmd);
                        DataSet dsSelect = new DataSet();
                        daSelect.Fill(dsSelect);
                        DataTable dtSelect = dsSelect.Tables[0];
                        Debug.Assert(dtSelect != null, "unable to find parent table");
                        Debug.Assert(dtSelect.Rows.Count == 1, "There can only be one parent record");
                        parent = Activator.CreateInstance(parentType);

                        Dictionary<string, MemberInfo> columnNameToMemberMappings =
                            this.GetColumnNameMemberMappingsForType(parentType);

                        for (int i = 0; i < dtSelect.Columns.Count; i++)
                        {
                            if (columnNameToMemberMappings.ContainsKey(dtSelect.Columns[i].ColumnName))
                            {
                                MemberInfo member = columnNameToMemberMappings[dtSelect.Columns[i].ColumnName];
                                if (member.MemberType == MemberTypes.Field)
                                {
                                    ((FieldInfo) member).SetValue(parent, dtSelect.Rows[0][i]);
                                }
                                else if (member.MemberType == MemberTypes.Property)
                                {
                                    ((PropertyInfo) member).SetValue(parent, dtSelect.Rows[0][i], null);
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                }
            }
            return parent;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns>dictionary of member name of current type --> List of all chilrdren members</returns>
        public Dictionary<string,List<MemberInfo>> GetChildrenReferences()
        {
            Dictionary<string, List<MemberInfo>> childrenRefs = new Dictionary<string, List<MemberInfo>>();
            string binFolderPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            FileInfo[] dllInfos = (new DirectoryInfo(binFolderPath)).GetFiles("*.dll", SearchOption.TopDirectoryOnly);
            // Dictionary<string, MemberInfo> columnToMemberMappings = this.GetColumnNameMemberMappingsForType(typeof(T));

            foreach(FileInfo dllInfo in dllInfos)
            {
                try
                {
                    Assembly assem = Assembly.LoadFile(dllInfo.FullName);
                    Type[] types = assem.GetTypes();
                    foreach(Type type in types)
                    {
                        object[] classAtts = type.GetCustomAttributes(typeof (OrmClassAttribute), false);
                        if(classAtts !=null && classAtts.Length==1)
                        {
                            FieldInfo[] fields = type.GetFields();
                            if (fields != null && fields.Length > 0)
                            {
                                foreach (FieldInfo field in fields)
                                {
                                    object[] fieldAtts = field.GetCustomAttributes(typeof (OrmForeignKey), false);
                                    if (fieldAtts != null && fieldAtts.Length == 1)
                                    {
                                        OrmForeignKey ormForeignKey = (OrmForeignKey) fieldAtts[0];
                                        if (ormForeignKey.ParentType == typeof (T))
                                        {
                                            if (childrenRefs.ContainsKey(ormForeignKey.ParentMemberName))
                                            {
                                                List<MemberInfo> foreignMembers =
                                                    childrenRefs[ormForeignKey.ParentMemberName];
                                                foreignMembers.Add(field);
                                                childrenRefs[ormForeignKey.ParentMemberName] = foreignMembers;
                                            }
                                            else
                                            {
                                                List<MemberInfo> foreignMembers = new List<MemberInfo>();
                                                foreignMembers.Add(field);
                                                childrenRefs.Add(ormForeignKey.ParentMemberName, foreignMembers);
                                            }
                                        }
                                    }
                                }
                            }

                            PropertyInfo[] props = type.GetProperties();
                            if (props != null && props.Length > 0)
                            {
                                foreach (PropertyInfo prop in props)
                                {
                                    object[] propAtts = prop.GetCustomAttributes(typeof(OrmForeignKey), false);
                                    if (propAtts != null && propAtts.Length == 1)
                                    {
                                        OrmForeignKey ormFKey = (OrmForeignKey) propAtts[0];
                                        if(ormFKey.ParentType==typeof(T))
                                        {
                                            if (childrenRefs.ContainsKey(ormFKey.ParentMemberName))
                                            {
                                                List<MemberInfo> foreignMembers =
                                                    childrenRefs[ormFKey.ParentMemberName];
                                                foreignMembers.Add(prop);
                                                childrenRefs[ormFKey.ParentMemberName] = foreignMembers;
                                            }
                                            else
                                            {
                                                List<MemberInfo> foreignMembers = new List<MemberInfo>();
                                                foreignMembers.Add(prop);
                                                childrenRefs.Add(ormFKey.ParentMemberName, foreignMembers);
                                            }
                                        }
                                    }
                                }
                            } 
                        }
                    }
                }
                catch(Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
            }
            return childrenRefs;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="keyValue"></param>
        /// <param name="childType"></param>
        /// <returns></returns>
        public List<object> GetReferencedChildrens(string connStr, string keyValue, Type childType)
        {
            List<object> children=new List<object>();
            Dictionary<string, List<MemberInfo>> childrenRefs = this.GetChildrenReferences();
            foreach(string memberName in childrenRefs.Keys)
            {
                List<MemberInfo> childrenMembers = childrenRefs[memberName];
                foreach(MemberInfo childMember in childrenMembers)
                {
                    if(childMember.DeclaringType==childType)
                    {
                        //OrmForeignKey fKey =
                        //    (OrmForeignKey) childMember.GetCustomAttributes(typeof (OrmForeignKey), false)[0];
                        Dictionary<string, MemberInfo> columnNameMemberMappings =
                            this.GetColumnNameMemberMappingsForType(childType);
                        OrmClassAttribute classAtt =
                            (OrmClassAttribute) childType.GetCustomAttributes(typeof (OrmClassAttribute), false)[0];
                        SqlConnection conn=new SqlConnection(connStr);
                        string childTableName = classAtt.TableName;
                        string childColumnName = childMember.Name;
                        foreach (string colName in columnNameMemberMappings.Keys)
                        {
                            if (columnNameMemberMappings[colName].Name == childMember.Name)
                            {
                                childColumnName = colName;
                                break;
                            }
                        }
                        TableColumn childKeyColumn = DbUtil.GetTableColumn(connStr, childTableName, childColumnName);
                        try
                        {
                            
                            conn.Open();
                            SqlCommand cmd = conn.CreateCommand();
                            cmd.CommandText = "select * from " + childTableName + " where " + childColumnName + "=" +
                                              FieldDataType.ToMssqlDataValue(childKeyColumn.DbType, keyValue);
                            SqlDataAdapter daChild=new SqlDataAdapter(cmd);
                            DataSet dsChild=new DataSet();
                            daChild.Fill(dsChild);
                            DataTable dtChild = dsChild.Tables[0];
                            if (dtChild != null && dtChild.Rows.Count > 0)
                            {
                                for (int i = 0; i < dtChild.Rows.Count; i++)
                                {
                                    object child = Activator.CreateInstance(childType);
                                    for(int k=0;k<dtChild.Columns.Count;k++)
                                    {
                                        if(columnNameMemberMappings.ContainsKey(dtChild.Columns[k].ColumnName))
                                        {
                                            MemberInfo member =
                                                columnNameMemberMappings[dtChild.Columns[k].ColumnName];
                                            if (member.MemberType == MemberTypes.Field)
                                            {
                                                ((FieldInfo)member).SetValue(child, dtChild.Rows[i][k]);
                                            }
                                            else if(member.MemberType==MemberTypes.Property)
                                            {
                                                ((PropertyInfo)member).SetValue(child,dtChild.Rows[i][k],null);
                                            }
                                        }
                                    }
                                    children.Add(child);
                                }
                            }
                        }
                        catch(Exception ex)
                        {
                            ExceptionHandler.HandlesException(ex);
                        }
                        finally
                        {
                            conn.Close();
                        }
                        break;
                    }
                }
            }
            return children;
        }
        #endregion
    }
}
